package com.songtech.base.universal.dao.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.songtech.base.universal.dao.beans.BeanParam;
import com.songtech.base.universal.dao.beans.Conditions;
import com.songtech.base.universal.dao.beans.Where;
import com.songtech.base.universal.dao.constant.BeanParamListEnum;
import com.songtech.base.universal.dao.constant.WhereEnum;

public class GetReflectionSqlUtil {
	/**
	 * 将传入的字段名组成一个以“,”隔开的字符串，字符串最后有一个“,”
	 * @param list 要查询的字段的集合
	 * @return 如果传入的list为空或长度为0，将返回空StringBuffer
	 */
	public static StringBuffer getParams(List<BeanParam> list){
		StringBuffer str = new StringBuffer();
		if(list == null || list.size() == 0){
			return str;
		}
		for(BeanParam p : list){
			str.append(" " + p.getFieldName() + ",");
		}
		return str;
	}
	
	/**
	 * 返回查询条件where子句
	 * @param list 生成查询条件的字段集合
	 * @return
	 */
	public static Map<String, Object> getWhereSql(List<BeanParam> list){
		Map<String, Object> map = new HashMap<>();
		if(list == null || list.size() == 0){
			return map;
		}
		
		StringBuffer sql = new StringBuffer();
		for(BeanParam p : list){
			if(sql.length() > 0) {
				sql.append(" and ");
			}
			sql.append(" " + p.getFieldName() + "=#{" + p.getFieldName() + "_0}");
			map.put(p.getFieldName() + "_0", p.getValue());
		}
		sql.insert(0, " where ");
		map.put("sql", sql.toString());
		
		return map;
	}
	
	/**
	 * 获取插入一条数据的sql
	 * <br>若主键不为null时，value为null则主键不插入
	 * @param list 要传入的list
	 * @param tableName 表名
	 * @param column 主键
	 * @return 如果传入的list为空或长度为0，将返回空串
	 */
	public static Map<String, Object> getInsertSql(List<BeanParam> list, String tableName, BeanParam column){
		Map<String, Object> map = new HashMap<>();
		if(list == null || list.size() == 0){
			return map;
		}
		// 插入时传入的主键为null则不插入
		if(column != null && column.getValue() == null){
			for(BeanParam b : list){
				if(b.getAttriName().equals(column.getAttriName())){
					list.remove(b);
					break;
				}
			}
		}
		
		StringBuffer sql = new StringBuffer();
		StringBuffer params = new StringBuffer();
		StringBuffer val = new StringBuffer();
		
		sql.append("insert into " + tableName);
		for(BeanParam p : list){
			params.append(" " + p.getFieldName() + ",");
			val.append("#{" + p.getFieldName() + "_0},");
			map.put(p.getFieldName() + "_0", p.getValue());
		}
		sql.append(" (" + params.toString().substring(0, params.length() - 1) + ") values ");
		sql.append(" (" + val.toString().substring(0, val.length() - 1) + ") ");
		map.put("sql", sql.toString());
		
		return map;
	}
	
	/**
	 * 获取批量插入的sql(没有对传入参数是否有值做验证)
	 * @param list List&lt;List&lt;Param&gt;&gt;实体数据list
	 * <br>每一个List&lt;Param&gt;都是一条把字段名和值一一对应的实体数据
	 * <br>若主键不为null时，value为null则主键不插入
	 * @param tableName 表名
	 * @param column 主键
	 * @return 如果传入的list为空或长度为0，将返回空串
	 */
	public static Map<String, Object> getInsertsSql(List<List<BeanParam>> list, String tableName, BeanParam column){
		Map<String, Object> map = new HashMap<>();
		if(list == null || list.size() == 0 || list.get(0).size() == 0){
			return map;
		}
		// 插入时传入的主键为null则不插入
		if(column != null && column.getValue() == null){
			for(List<BeanParam> l : list){
				// 多条语句插入，每一条语句的主键从list中删除
				for(BeanParam p : l){
					if(p.getFieldName().equals(column.getFieldName())){
						l.remove(p);
						break;
					}
				}
			}
		}
		
		StringBuffer sql = new StringBuffer();
		StringBuffer params = new StringBuffer();
		StringBuffer val = new StringBuffer();
		
		sql.append("insert into " + tableName);
		int i = 0;
		for(List<BeanParam> l : list){
			for(BeanParam p : l){
				// 获取字段名
				if(i == 0){
					params.append(" " + p.getFieldName() + ",");
				}
				val.append("#{" + p.getFieldName() + "_" + i + "},");
				map.put(p.getFieldName() + "_" + i, p.getValue());
			}
			// 循环第一次时加入要写入的字段名
			if(i == 0){
				sql.append(" (" + params.toString().substring(0, params.length() - 1) + ") values ");
			}
			// 多条插入的数据以“,”隔开
			if(i > 0){
				sql.append(",");
			}
			// 要插入的数据
			sql.append(" (" + val.toString().substring(0, val.length() - 1) + ") ");
			// 清空
			val.delete(0, val.length());
			i ++;
		}
		map.put("sql", sql.toString());
		
		return map;
	}
	
//	/**
//	 * 获取删除数据的sql
//	 * @param tableName 要删除的表
//	 * @param column 字段名称
//	 * @param id 字段对应的值
//	 * @return
//	 */
//	public static Map<String, Object> getDeleteSql(String tableName, BeanParam column){
//		Map<String, Object> map = new HashMap<>();
//		if(tableName != null && column != null && column.getFieldName() != null && column.getValue() != null){
//			String sql = "delete from " + tableName + " where " + column.getFieldName() + "=#{" + column.getFieldName() + "_0}";
//			map.put("sql", sql);
//			map.put(column.getFieldName() + "_0", column.getValue());
//		}
//		return map;
//	}
//	
//	/**
//	 * 获取删除数据的sql
//	 * @param list 作为生成条件的list
//	 * @param tableName 表名
//	 * @return
//	 */
//	public static Map<String, Object> getDeleteSql(List<BeanParam> list, String tableName){
//		Map<String, Object> map = new HashMap<>();
//		if(tableName != null){
//			map = GetReflectionSqlUtil.getWhereSql(list);
//			map.put("sql", "delete from " + tableName + map.get("sql").toString());
//		}
//		return map;
//	}
	
	/**
	 * 获取删除数据的sql
	 * @param list 作为生成条件的list
	 * @param tableName 表名
	 * @return
	 */
	public static Map<String, Object> getDeleteSql(Where where, String tableName, Class<?> cls){
		Map<String, Object> map = new HashMap<>();
		if(tableName != null){
			map = where.toMap(cls);
			map.put("sql", "delete from " + tableName + " where " + map.get("sql").toString());
		}
		return map;
	}
	
	/**
	 * 获取修改数据的sql
	 * @param list 要修改的字段的list(包括字段名和值)
	 * @param tableName 表名
	 * @param column 作为查询条件的字段
	 * @return
	 */
	public static Map<String, Object> getUpdateSql(List<BeanParam> list, String tableName, BeanParam column, Class<?> cls){
		Map<String, Object> map = new HashMap<>();
		if(tableName != null && list != null && column != null){
//			StringBuffer col = new StringBuffer();
//			for(BeanParam p : list){
//				col.append(" " + p.getFieldName() + "=#{" + p.getFieldName() + "_0},");
//				map.put(p.getFieldName() + "_0", p.getValue());
//			}
//			String sql = "update " + tableName + " set " + col.toString().substring(0, col.length() - 1) + " where " + column.getFieldName() + "=#{" + column.getFieldName() + "_0}";
//			map.put("sql", sql);
//			map.put(column.getFieldName() + "_0", column.getValue());
			Where where = new Where(column);
			map = GetReflectionSqlUtil.getUpdateByWhereSql(list, tableName, where, cls);
		}
		return map;
	}
	
	/**
	 * 获取根据条件修改数据的sql
	 * @param list 要修改的字段的list(包括字段名和值)
	 * @param tableName 表名
	 * @param column 作为查询条件的字段
	 * @param cls
	 * @return
	 */
	public static Map<String, Object> getUpdateByWhereSql(List<BeanParam> list, String tableName, Where where, Class<?> cls){
		Map<String, Object> map = new HashMap<>();
		if(tableName != null && list != null && where != null){
			map = where.toMap(cls);
			StringBuffer col = new StringBuffer();
			for(BeanParam p : list){
				col.append(" " + p.getFieldName() + "=#{" + p.getFieldName() + "_0},");
				map.put(p.getFieldName() + "_0", p.getValue());
			}
			String sql = "update " + tableName + " set " + col.toString().substring(0, col.length() - 1) + " where " + map.get("sql").toString();
			map.put("sql", sql);
		}
		return map;
	}
	
	/**
	 * 获取根据主键查询数据的sql
	 * @param tableName 表名
	 * @param list 要查询的字段list
	 * @param column 作为查询条件的字段
	 * @return
	 */
	public static Map<String, Object> getSelectSql(String tableName, List<BeanParam> list, BeanParam column){
		Map<String, Object> map = new HashMap<>();
		if(tableName != null && list != null && column != null){
			StringBuffer col = new StringBuffer();
			for(BeanParam p : list){
				col.append(" " + p.getFieldName() + ",");
			}
			String sql = "select " + col.toString().substring(0, col.length() - 1) + " from " + tableName + " where " + column.getFieldName() + "=#{" + column.getFieldName() + "_0}";
			map.put(column.getFieldName() + "_0", column.getValue());
			map.put("sql", sql);
		}
		return map;
	}
	
	/**
	 * 获取根据主键查询数据的sql
	 * @param tableName 表名
	 * @param fileName 要查询的字段
	 * @param column 作为查询条件的字段
	 * @return
	 */
	public static Map<String, Object> selectFieldById(String tableName, String fileName, BeanParam column){
		if(StringUtils.isEmpty(fileName)){
			return new HashMap<>();
		}
		List<BeanParam> list = new ArrayList<>();
		list.add(new BeanParam(fileName));
		return GetReflectionSqlUtil.getSelectSql(tableName, list, column);
	}
	
	/**
	 * 获取根据指定条件查询数据条数的sql
	 * @param list
	 * @param tableName
	 * @return
	 */
	public static Map<String, Object> getIsExistSql(List<BeanParam> list, String tableName){
		Map<String, Object> map = new HashMap<>();
		if(tableName != null && list != null){
			StringBuffer col = new StringBuffer();
			for(BeanParam p : list){
				col.append(" " + p.getFieldName() + "=#{" + p.getFieldName() + "_0} and ");
				map.put(p.getFieldName() + "_0", p.getValue());
			}
			String sql = "select count(1) from " + tableName + " where " + col.toString().substring(0, col.length() - 4);
			map.put("sql", sql);
		}
		return map;
	}
	
	/**
	 * 获取根据指定条件查询数据指定字段的sql
	 * @param list
	 * @param tableName
	 * @param fileName 要查询的字段
	 * @param cls
	 * @return
	 */
	public static Map<String, Object> getSelectListFileSql(Where where, String tableName, String fileName, Class<?> cls){
		if(StringUtils.isEmpty(fileName)){
			return new HashMap<>();
		}
		List<BeanParam> list = new ArrayList<>();
		list.add(new BeanParam(fileName));
		return GetReflectionSqlUtil.getSelectListSql(where, tableName, list, cls);
	}
	
	/**
	 * 获取根据指定条件查询数据的sql
	 * @param where
	 * @param tableName
	 * @param list 要查询的字段list
	 * @param cls
	 * @return
	 */
	public static Map<String, Object> getSelectListSql(Where where, String tableName, List<BeanParam> list, Class<?> cls){
		Map<String, Object> map = new HashMap<>();
		if(tableName != null && cls != null && where != null){
			map = where.toMap(cls);
			StringBuffer col = GetReflectionSqlUtil.getParams(list);
			String sql = "select " + col.toString().substring(0, col.length() - 1) + " from " + tableName + " where " + map.get("sql").toString();
			map.put("sql", sql);
		}
		return map;
	}
	
	/**
	 * 获取根据指定条件查询数据条数的sql
	 * @param list
	 * @param tableName
	 * @param cls
	 * @return
	 */
	public static Map<String, Object> getCountSql(Where where, String tableName, Class<?> cls){
		Map<String, Object> map = new HashMap<>();
		if(tableName != null && where != null){
			map = where.toMap(cls);
			String sql = "select count(1) from " + tableName + " where " + map.get("sql").toString();
			map.put("sql", sql);
		}
		return map;
	}
	
	/**
	 * 将传入的bean list组成where条件
	 * @param map 为null时创建一个新的hashmap
	 * @param list
	 * @param beanParamListEnum
	 * @return
	 */
	public static Map<String, Object> getBeanParamListToSql(Map<String, Object> map, List<BeanParam> list, BeanParamListEnum beanParamListEnum){
		if(map == null){
			map = new HashMap<>();
		}
		if(list != null && list.size() > 0){
			StringBuffer col = new StringBuffer();
			for(BeanParam p : list){
				col.append(" " + p.getFieldName() + "=#{" + p.getFieldName() + "_" + beanParamListEnum.toString() + "} and ");
				map.put(p.getFieldName() + "_" + beanParamListEnum.toString(), p.getValue());
			}
			map.put("sql", GetReflectionSqlUtil.getWhereSql(map, col.toString().substring(0, col.length() - 4)));
		}
		return map;
	}
	
	/**
	 * 将传入的wherelist组成where条件
	 * @param map 为null时创建一个新的hashmap
	 * @param where
	 * @return
	 */
	public static Map<String, Object> getWhereListToSql(Map<String, Object> map, List<Conditions<?>> list){
		if(map == null){
			map = new HashMap<>();
		}
		if(list != null && list.size() > 0){
			StringBuffer col = new StringBuffer();
			int i = 0;
			for(Conditions<?> w : list){
				GetReflectionSqlUtil.setWhereValue(map, col, w, i);
				i ++;
			}
			map.put("sql", GetReflectionSqlUtil.getWhereSql(map, col.toString().substring(0, col.length() - 4)));
		}
		return map;
	}
	
	/**
	 * 组装sql条件
	 * @param map
	 * @param sql
	 * @return
	 */
	private static String getWhereSql(Map<String, Object> map, String sql){
		Object o = map.get("sql");
		// 每两个sql间使用and连接
		return o == null ? sql : " (" + o.toString() + ") and (" + sql + ") ";
	}
	
	/**
	 * 设置查询条件
	 * @param map
	 * @param col
	 * @param where
	 * @param i
	 */
	private static void setWhereValue(Map<String, Object> map, StringBuffer col, Conditions<?> where, int i){
		if(where != null && where.getValue() != null && where.getValue().size() > 0){
			col.append(" " + where.getFieldName());
			switch (where.getWhere()) {
				case IN:
					StringBuffer sbf = new StringBuffer();
					int j = 0;
					for(Object o : where.getValue()){
						sbf.append("#{" + where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "_" + j + "},");
						map.put(where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "_" + j, o);
						j ++;
					}
					col.append(" in (" + sbf.toString().substring(0, col.length() - 1) + ") ");
					break;
				case LIKE:// like '%value%'
					col.append(" like concat(concat('%',#{" + where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "_0}),'%') ");
					map.put(where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "_0", where.getValue().get(0));
					break;
				case LTLIKE:// like '%value'
					col.append(" like concat('%', #{" + where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "_1}) ");
					map.put(where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "_1", where.getValue().get(0));
					break;
				case GTLIKE:// like 'value%'
					col.append(" like concat(#{" + where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "_2}, '%') ");
					map.put(where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "_2", where.getValue().get(0));
					break;
				default:
					col.append(" " + where.getWhere().toString() + " #{" + where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i + "} ");
					map.put(where.getFieldName() + "_" + BeanParamListEnum.WHERE_WHERE + "_" + i, where.getValue().get(0));
					break;
			}
			col.append(" and ");
		}
	}
	
	public static void main(String[] args) {
//		List<BeanParam> list = new java.util.ArrayList<>();
//		list.add(new BeanParam("test1", "test1", String.class.getName(), ""));
//		list.add(new BeanParam("test2", "test2", String.class.getName(), ""));
//		list.add(new BeanParam("test3", "test3", String.class.getName(), ""));
//		list.add(new BeanParam("test4", "test4", String.class.getName(), ""));
//		list.add(new BeanParam("test5", "test5", String.class.getName(), ""));
//		list.add(new BeanParam("test6", "test6", String.class.getName(), ""));
//		
//		System.out.println(GetReflectionSqlUtil.getParams(list));
		Map<String, Object> map = new HashMap<>();
		Object o = map.get("sql");
		String sql = o == null ? "" : o.toString();
		System.out.println(sql);
		WhereEnum w = WhereEnum.LIKE;
		System.out.println(w == WhereEnum.LTLIKE);
	}
}
